<?php
/*********************************************************************************
 * This file is part of KReporter. KReporter is an enhancement developed 
 * by KINAMU Business Solutions AG. All rights ar (c) 2010 by KINAMU Business
 * Solutions AG.
 *
 * KReporter is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * KReporter is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with KReporter.  If not, see <http://www.gnu.org/licenses/>.
 * 
 * You can contact KINAMU Business Solutions AG at Am Concordepark 2/F12
 * A-2320 Schwechat or via email at office@kinamu.com
 * 
 * The interactive user interfaces in modified source and object code versions
 * of this program must display Appropriate Legal Notices, as required under
 * Section 5 of the GNU General Public License version 3.
 * 
 * In accordance with Section 7(b) of the GNU General Public License version 3,
 * these Appropriate Legal Notices must retain the Name KINAMU in the Name
 * of the Module which needs to be visible to the User.
 * 
 ********************************************************************************/
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
require_once('modules/KReports/utils.php');

class KReport extends SugarBean {
    var $field_name_map;

		var $id;
	var $date_entered;
	var $date_modified;
	var $assigned_user_id;
	var $modified_user_id;
	var $created_by;
	var $created_by_name;
	var $modified_by_name;

	var $team_id;

	var $description;
	var $name;
	var $status;
	var $assigned_user_name;

	var $team_name;

	var $table_name = "kreports";
	var $object_name = "KReport";
	var $module_dir = 'KReports';

	var $importable = true;
		
	
		var $tablePath;
	var $joinSegments;
	var $rootGuid;
	var $fromString;
	
	var $whereOverride;
	
		var $fieldNameMap;
	


			var $evalSQLFunctions = true;
	
		var $maxDepth; 

	function KReport() {
		parent::SugarBean();
	}
	
	function bean_implements($interface){
		switch($interface){
			case 'ACL':return true;
		}
		return false;
	}
	
	function get_summary_text()
	{
		return $this->name;
	}

	
	
	
	function getEnumValues($fieldId)
	{
		global $app_list_strings;
		
				$arrayList =  $this->json_decode_kinamu( html_entity_decode_utf8($this->listfields));
		
				$i = 0;
		while($arrayList[$i]['fieldid'] != $fieldId && $i < count($arrayList))
		   $i++;
		   
				if($arrayList[$i]['fieldid'] == $fieldId)
		{
			$fieldName = substr($arrayList[$i]['path'], strrpos($arrayList[$i]['path'], "::") + 2, strlen($arrayList[$i]['path']));
			$pathName = substr($arrayList[$i]['path'], 0, strrpos($arrayList[$i]['path'], "::"));
		
		    $fieldArray = explode(':', $fieldName);
									if(isset($this->joinSegments[$pathName]) && $this->joinSegments[$pathName]['object']->field_name_map[$fieldArray[1]]['type'] == 'enum' )
			{
				return $app_list_strings[$this->joinSegments[$pathName]['object']->field_name_map[$fieldArray[1]]['options']];
			}
			else
			{
				return '';
			}
		}
		else
		{
			return '';
		}
		
	}
	
	
	function json_decode_kinamu($json)
	{ 
	    	    $comment = false;
	    $out = '$x=';
	   
	    for ($i=0; $i<strlen($json); $i++)
	    {
	        if (!$comment)
	        {
	            if ($json[$i] == '{' or $json[$i] == '[')        $out .= ' array(';
	            else if ($json[$i] == '}' or $json[$i] == ']')    $out .= ')';
	            else if ($json[$i] == ':')    $out .= '=>';
	            else                         $out .= $json[$i];           
	        }
	        else $out .= $json[$i];
	        if ($json[$i] == '"')    $comment = !$comment;
	    }
	    eval($out . ';');
	    return $x;
	}  

	function fill_in_additional_detail_fields() 
	{
		parent::fill_in_additional_detail_fields();
		if($this->report_module != '')
		{
						
					}	
	}
	
	
	function randomstring(){
		$len = 10;
		$base='ABCDEFGHKLMNOPQRSTWXYZabcdefghjkmnpqrstwxyz123456789';
		$max=strlen($base)-1;
		$returnstring = '';
		mt_srand((double)microtime()*1000000);
		while (strlen($returnstring)<$len+1)
		  $returnstring.=$base{mt_rand(0,$max)};
		  
		return $returnstring;
		
	}
	
	
	function switchJoinType($jointype){
				switch($jointype){
			case "optional":
				return ' LEFT JOIN ';
				break;
			case "required":
				return ' INNER JOIN ';
				break;
			case "notexisting":
				return ' LEFT JOIN ';
				break;
		}
	}
	
	
	
	function build_path(){
		$arrayWhere =  $this->json_decode_kinamu( html_entity_decode_utf8($this->whereconditions));
		$arrayList =  $this->json_decode_kinamu( html_entity_decode_utf8($this->listfields));
		$arrayWhereGroups = $this->json_decode_kinamu( html_entity_decode_utf8($this->wheregroups));
		
		if((is_array($arrayWhere) && count($arrayWhere) > 0) || (is_array($arrayList) && count($arrayList) > 0) )
		{
			
						foreach($arrayWhere as $thisWhere)
			{
								
								$flagNotExists = false;
				reset($arrayWhereGroups);
				foreach($arrayWhereGroups as $thisWhereArray)
				{
					if($thisWhereArray['id'] == $thisWhere['groupid'] && $thisWhereArray['notexists'] == '1')
						$flagNotExists = false;
				}
				
																$this->addPath($thisWhere['path'], $this->switchJoinType($thisWhere['jointype']));
			}
			
						foreach($arrayList as $thisList)
			{
				$this->addPath($thisList['path'], $this->switchJoinType($thisList['jointype']));
			}
			
		}
		
	}
	
	
	function addPath($path, $jointype){
				$fieldPos = strpos($path, "::field");
		$path = substr($path, 0, $fieldPos);
		
		if(!isset($this->tablePath[$path]))
		{
			$this->tablePath[$path] = $jointype;
		}
		else
		{
						if($this->tablePath[$path] == 'LEFT JOIN' && $jointype == 'INNER JOIN')
				$this->tablePath[$path] = $jointype;
		}
		
										while($sepPos = strrpos($path, "::"))
		{
						$path = substr($path, 0, $sepPos);
			
						if(!isset($this->tablePath[$path]))
			{
				$this->tablePath[$path] = $jointype;
			}
			else
			{
								if($this->tablePath[$path] == 'LEFT JOIN' && $jointype == 'INNER JOIN')
					$this->tablePath[$path] = $jointype;
			}
		}
		
	}	
	
	
	function get_table_for_module($module){
		global $beanList, $beanFiles;
		require_once($beanFiles[$beanList[$module]]);
		$thisModule = new $beanList[$module];
		return $thisModule->table_name;
	}
	
	
	function build_joinsegments(){
		global $db, $app_list_strings, $beanList, $beanFiles;
		
				$this->rootGuid = $this->randomstring();
		
		$this->joinSegments = array();
		$this->maxDepth = 0;
		
		
		foreach($this->tablePath as $thisPath => $thisPathJoinType)
		{
												if(substr_count($thisPath, '::') > $this->maxDepth) $this->maxDepth = substr_count($thisPath, '::');
			
			while(strpos($thisPath, '::') && !isset($this->joinSegments[$thisPath]))
			{
								$this->joinSegments[$thisPath] = array( 'alias' => $this->randomstring(), 'level' => substr_count($thisPath, '::'), 'jointype' => $thisPathJoinType);
				
								$thisPath = substr($thisPath, strrpos($thisPath, "::"));
			}
		}
				$this->fromString = 'from ' . $this->get_table_for_module($this->report_module) .' as ' . $this->rootGuid;
				

	    				$this->joinSegments['root:' . $this->report_module] = array( 'alias' => $this->rootGuid, 'level' => 0);
		 
				require_once($beanFiles[$beanList[$this->report_module]]);
		$this->joinSegments['root:' . $this->report_module]['object'] = new $beanList[$this->report_module]();
			
				if($this->joinSegments['root:' . $this->report_module]['object']->hasCustomFields())
		{
		     $this->joinSegments['root:' . $this->report_module]['customjoin'] = $this->randomstring();
		     $this->fromString .= ' LEFT JOIN ' . $this->get_table_for_module($this->report_module) . '_cstm as ' . $this->joinSegments['root:' . $this->report_module]['customjoin'] . '  ON ' . $this->rootGuid . '.id = ' . $this->joinSegments['root:' . $this->report_module]['customjoin'] . '.id_c';
		}
		
						$levelCounter = 1;		
		
		if(is_array($this->joinSegments))
		{
		  
		  while($levelCounter <= $this->maxDepth)
		  {
		  			  	reset($this->joinSegments);
		  	
		  	foreach($this->joinSegments as $thisPath => $thisPathDetails)
		  	{
		  				  		if($thisPathDetails['level'] == $levelCounter)
		  		{
		  					  			$rightPath = substr($thisPath, strrpos($thisPath, "::") + 2, strlen($thisPath));
		  			$leftPath = substr($thisPath, 0, strrpos($thisPath, "::"));
		  			
		  					  			$rightArray = explode(':',$rightPath);
		  			$leftArray = explode(':', $leftPath);
		  			
		  								if(!($this->joinSegments[$leftPath]['object'] instanceof $beanList[$rightArray[1]])) die('fatal Error in Join');

										$this->joinSegments[$leftPath]['object']->load_relationship($rightArray[2]);
															$join_params = array(
						'join_type' => $thisPathDetails['jointype'], 
						'right_join_table_alias' => $this->joinSegments[$leftPath]['alias'], 
						'left_join_table_alias' => $this->joinSegments[$leftPath]['alias'], 
						'join_table_link_alias' => $this->randomstring() , 
						'join_table_alias' => $this->joinSegments[$thisPath]['alias']					
					);
					
					$linkJoin = $this->joinSegments[$leftPath]['object']->$rightArray[2]->getJoin($join_params);
										
					$this->fromString .= ' ' . $linkJoin;
					
							 			require_once($beanFiles[$beanList[$this->joinSegments[$leftPath]['object']->$rightArray[2]->getRelatedModuleName()]]);
		 			$this->joinSegments[$thisPath]['object'] = new $beanList[$this->joinSegments[$leftPath]['object']->$rightArray[2]->getRelatedModuleName()]();
					
		  								if($this->joinSegments[$thisPath]['object']->hasCustomFields())
					{
					     $this->joinSegments[$thisPath]['customjoin'] = $this->randomstring();
					     $this->fromString .= ' LEFT JOIN ' . $this->joinSegments[$thisPath]['object']->table_name . '_cstm as ' . $this->joinSegments[$thisPath]['customjoin'] . ' ON ' . $this->joinSegments[$thisPath]['alias'] . '.id = ' . $this->joinSegments[$thisPath]['customjoin'] . '.id_c';
					}
		 			
		  		}
		  	}
		  	
		  			  	$levelCounter++;
		  }
		  
		}
	}
	
	
	function getFieldNameFromPath($pathName)
	{
		return substr($pathName, strrpos($pathName, "::") + 2, strlen($pathName));
	}
	
	
	function getPathNameFromPath($pathName)
	{
		return substr($pathName, 0, strrpos($pathName, "::"));
	}
	
	
	
	function buildSelectFields(){
					global $db, $app_list_strings, $beanList, $beanFiles;
		
				$arrayList =  $this->json_decode_kinamu( html_entity_decode_utf8($this->listfields));

		$selectString = 'SELECT ' . $this->rootGuid . '.id as sugarRecordId';
		
				foreach($this->joinSegments as $joinsegment)
		{
			$selectString .= ', ' . $joinsegment['alias'] . '.id as ' . $joinsegment['alias'] . 'id';
		}
		
		if(is_array($arrayList))
		{
			foreach($arrayList as $thisList)
			{
									$fieldName = substr($thisList['path'], strrpos($thisList['path'], "::") + 2, strlen($thisList['path']));
		  			$pathName = substr($thisList['path'], 0, strrpos($thisList['path'], "::"));
		  			
		  			$fieldArray = explode(':', $fieldName);
		  			
		  			
		  					  			if($thisList['sqlfunction'] == '-' || !$this->evalSQLFunctions)
		  				$selectString .= ', ' . $this->get_field_name($pathName, $fieldArray[1], $thisList['fieldid'], ($thisList['link'] == 'yes') ? true : false);
		  			else
		  				$selectString .= ', ' . $thisList['sqlfunction'] . '(' . $this->get_field_name($pathName, $fieldArray[1], $thisList['fieldid'], ($thisList['link'] == 'yes') ? true : false) . ')';
		  			
		  			if(isset($thisList['fieldid']) && $thisList['fieldid'] != '' )
		  			{
		  				$selectString .= " as '" . $thisList['fieldid'] . "'";
		  			}
		  			$selectFields[] = trim($thisList['name'], ':');
			}
		}
		else 
		{
			$selectString .= '*';
		}
		
		return $selectString;
	}
	
	
	function build_sql_string(){
		global $db, $app_list_strings, $beanList, $beanFiles;
		
		$arrayWhere =  $this->json_decode_kinamu( html_entity_decode_utf8($this->whereconditions));
		$arrayList =  $this->json_decode_kinamu( html_entity_decode_utf8($this->listfields));
		$arrayWhereGroups = $this->json_decode_kinamu( html_entity_decode_utf8($this->wheregroups));
		
				if((is_array($arrayWhere) && count($arrayWhere) > 0) || (is_array($arrayList) && count($arrayList) > 0) )
		{
						$this->build_path();
			
						$this->build_joinsegments();
			
						$selectString = $this->buildSelectFields();
			
						$whereString = $this->buildWhereClause();
	
						$groupbyString = $this->buildGroupByClause();
		
						$orderbyString = $this->buildOrderByClause();
			
						return array('select' => $selectString, 'from' => $this->fromString, 'where' => $whereString ,'fields' => $selectFields, 'groupby' => $groupbyString, 'orderby' => $orderbyString);
				
		}
		else 
		{
			return array('select' => '', 'from' => '', 'fields' => '');
		}
	}
	

	
	
	function buildWhereClauseForLevel($thisLevel, $completeArray)
	{
		$whereClause = '';
		
				foreach($completeArray as $currentEntry)
		{
			if($currentEntry['parent'] == $thisLevel['id'])
			{
				$thisLevel['children'][$currentEntry['id']] = $currentEntry;
			}
		}
		
				if(is_array($thisLevel['children']))
			$whereClauseChildren = $this->buildWhereClauseForChildren($thisLevel['children'], $thisLevel['type'], $completeArray);
		else
			$whereClauseChildren = '';
			
				if(isset($thisLevel['whereClause']) && $thisLevel['whereClause'] != '')
		{
				$whereClause = $thisLevel['whereClause'];
		}	
		
				if($whereClauseChildren != '')
		{
			if($whereClause != '')
				$whereClause .= ' ' . $thisLevel['type'] . ' ' . $whereClauseChildren;
			else
				$whereClause = $whereClauseChildren;
		}
		
				if($whereClause != '')
			$whereClause = '(' . $whereClause . ')';
			
				return  $whereClause;

		
	}
	
	
	function buildWhereClauseForChildren($thisChildren, $thisOperator, $completeArray)
	{
		$whereClause = '';
		foreach($thisChildren as $thisChild)
		{
			$childWhereClause = $this->buildWhereClauseForLevel($thisChild, $completeArray);
						if($childWhereClause != '')
			{
				if($whereClause != '')
					$whereClause .= ' ' . $thisOperator . ' ' . $childWhereClause;
				else
					$whereClause = $childWhereClause;
			}
		}
		
				return $whereClause;
	}
	
	
	function get_field_name($path, $field, $fieldid, $link = false)
	{
		    $thisAlias = ($this->joinSegments[$path]['object']->field_name_map[$field]['source'] == 'custom_fields') ? $this->joinSegments[$path]['customjoin'] : $this->joinSegments[$path]['alias'];
		
		    global $beanList;
		    $thisModule = array_search($this->joinSegments[$path]['object']->object_name, $beanList);
		    		    $this->fieldNameMap[$fieldid] = array('islink' => $link,  'tablealias' => $thisAlias,  'type' => ($this->joinSegments[$path]['object']->field_name_map[$field]['type'] == 'kreporter') ? $this->joinSegments[$path]['object']->field_name_map[$field]['kreporttype'] : $this->joinSegments[$path]['object']->field_name_map[$field]['type'], 'module' => $thisModule);
		    
	  		if($this->joinSegments[$path]['object']->field_name_map[$field]['type'] == 'kreporter')
  			{
  				return '(' . str_replace('$', $thisAlias,  $this->joinSegments[$path]['object']->field_name_map[$field]['eval']) . ')';
  			}
  			else
  			{
  				return  $thisAlias . '.' . $field;
  			}
	}
	
	function formatFields($fieldArray, $buildlinks = true){
		require_once('modules/Currencies/Currency.php');
		foreach($fieldArray as $fieldID => $fieldValue)
		{
			if(isset($this->fieldNameMap[$fieldID]))
			{
				switch($this->fieldNameMap[$fieldID]['type'])
				{
					case 'currency':
												$fieldValue = currency_format_number($fieldValue);
						break;
				}
				
								if($buildlinks && $this->fieldNameMap[$fieldID]['islink'])
				{
					$fieldValue = '<a href="index.php?module=' . $this->fieldNameMap[$fieldID]['module'] . '&action=DetailView&record=' . $fieldArray[$this->fieldNameMap[$fieldID]['tablealias'] . 'id'] .'" class="tabDetailViewDFLink">' . $fieldValue . '</a>';
				}
			}
			
			
			$returnArray[$fieldID] = $fieldValue;
		}
		
		return $returnArray;
	}
	
	function getWhereOperatorClause($operator, $fieldname, $fieldid, $path,  $value, $valuekey, $valueto)
	{
  			$thisWhereString .= $this->get_field_name($path, $fieldname, $fieldid);
  			
  			  			if(isset($valuekey) && $valuekey != '' && $valuekey != 'undefined') $value = $valuekey;
  			
  			  			switch($operator)
  			{
  				case 'equals':
  					$thisWhereString .= ' = \'' . $value . '\'';
  					break;
  				case 'notequal':
  					$thisWhereString .= ' <> \'' . $value . '\'';
  					break;
  				case 'greater':
  					$thisWhereString .= ' > \'' . $value . '\'';
  					break;	
				case 'less':
  					$thisWhereString .= ' < \'' . $value . '\'';
  					break;	
  				case 'greaterequal':
  					$thisWhereString .= ' >= \'' . $value . '\'';
  					break;	
				case 'lessequal':
  					$thisWhereString .= ' <= \'' . $value . '\'';
  					break;
  				case 'starts':
  					$thisWhereString .= ' LIKE \'' . $value . '%\'';
  					break;
  				case 'notstarts':
  					$thisWhereString .= ' NOT LIKE \'' . $value . '%\'';
  					break;  					
   				case 'contains':
  					$thisWhereString .= ' LIKE \'%' . $value . '%\'';
  					break;
   				case 'notcontains':
  					$thisWhereString .= ' NOT LIKE \'%' . $value . '%\'';
  					break;  					
   				case 'between':
   					$thisWhereString .= ' >= \'' . $value . '\' AND ' . $this->get_field_name($path, $fieldname, $fieldid) . '<=\'' . $valueto . '\'';
   					break;
  				case 'isempty':
  					$thisWhereString .= ' = \'\'';
  					break;
   				case 'isemptyornull':
  					$thisWhereString .= ' = \'\' OR ' . $this->get_field_name($path, $fieldname, $fieldid) . ' IS NULL';
  					break;
   				case 'isnull':
  					$thisWhereString .= ' IS NULL';
  					break;  					
	  			case 'isnotempty':
	  				$thisWhereString .= ' <> \'\' AND ' . $this->get_field_name($path, $fieldname, $fieldid) .' is not null';
	  				break;		
	  			case 'oneof':
	  				$thisWhereString .= ' IN (\''. str_replace(',', '\',\'', $value) . '\')';
	  				break;	  					
	  			case 'oneofnot':
	  				$thisWhereString .= ' NOT IN (\''. str_replace(',', '\',\'', $value) . '\')';
	  				break;
	  			case 'oneofnotornull':
	  				$thisWhereString .= ' NOT IN (\''. str_replace(',', '\',\'', $value) . '\') OR ' . $this->get_field_name($path, $fieldname, $fieldid) . ' IS NULL'; ;
	  				break;	  				
	  			case 'thismonth':
	  				$dateArray = getdate();
	  				$fromDate = date('Y-m-d',mktime(0, 0, 0, $dateArray['mon'], 1, $dateArray['year']));
	  				$toDate = (($dateArray['mon'] + 1) > 12) ? date('Y-m-d',mktime(0, 0, 0, $dateArray['mon'] - 11, 1, $dateArray['year'] + 1)): date('Y-m-d',mktime(0, 0, 0, $dateArray['mon'] + 1, 1, $dateArray['year']));
	  				$thisWhereString .= ' >= \'' . $fromDate . ' 00:00:00\' AND ' . $this->get_field_name($path, $fieldname, $fieldid) .' < \'' . $toDate . ' 00:00:00\'';
	  				break;	   	
	  			case 'next3month':
	  				$dateArray = getdate();
	  				$fromDate = date('Y-m-d',mktime(0, 0, 0, $dateArray['mon'], 1, $dateArray['year']));
	  				$toDate = (($dateArray['mon'] + 3) > 12) ? date('Y-m-d',mktime(0, 0, 0, $dateArray['mon'] - 8, 1, $dateArray['year'] + 1)): date('Y-m-d',mktime(0, 0, 0, $dateArray['mon'] + 3, 1, $dateArray['year']));
	  				$thisWhereString .= ' >= \'' . $fromDate . ' 00:00:00\' AND ' . $this->get_field_name($path, $fieldname, $fieldid) .' < \'' . $toDate . ' 00:00:00\'';
	  				break;	 
	  			case 'thisyear':
	  				$dateArray = getdate();
	  				$fromDate = date('Y-m-d',mktime(0, 0, 0, 1, 1, $dateArray['year']));
	  				$toDate = date('Y-m-d',mktime(0, 0, 0, 1, 1, $dateArray['year'] + 1));
	  				$thisWhereString .= ' >= \'' . $fromDate . ' 00:00:00\' AND ' . $this->get_field_name($path, $fieldname, $fieldid) .' < \'' . $toDate . ' 00:00:00\'';
	  				break;		  					  				
	  			case 'lastmonth':
	  				$dateArray = getdate();
	  				$fromDate= (($dateArray['mon'] - 1) < 1) ? date('Y-m-d',mktime(0, 0, 0, $dateArray['mon'] + 11, 1, $dateArray['year'] - 1)) : date('Y-m-d h:i:s',mktime(0, 0, 0, $dateArray['mon'] - 1, 1, $dateArray['year']));
	  				$toDate= date('Y-m-d',mktime(0, 0, 0, $dateArray['mon'], 1, $dateArray['year']));
	  				$thisWhereString .= ' >= \'' . $fromDate . ' 00:00:00\' AND ' . $this->get_field_name($path, $fieldname, $fieldid) .' < \'' . $toDate . ' 00:00:00\'';
	  				break;	
	  			case 'last3month':
	  				$dateArray = getdate();
	  				$fromDate= (($dateArray['mon'] - 3) < 1) ? date('Y-m-d',mktime(0, 0, 0, $dateArray['mon'] + 8, 1, $dateArray['year'] - 1)) : date('Y-m-d h:i:s',mktime(0, 0, 0, $dateArray['mon'] - 3, 1, $dateArray['year']));
	  				$toDate= date('Y-m-d',mktime(0, 0, 0, $dateArray['mon'], 1, $dateArray['year']));
	  				$thisWhereString .= ' >= \'' . $fromDate . ' 00:00:00\' AND ' . $this->get_field_name($path, $fieldname, $fieldid) .' < \'' . $toDate . ' 00:00:00\'';
	  				break;	
	  			case 'lastyear':
	  				$dateArray = getdate();
	  				$fromDate = date('Y-m-d',mktime(0, 0, 0, 1, 1, $dateArray['year'] - 1));
	  				$toDate = date('Y-m-d',mktime(0, 0, 0, 1, 1, $dateArray['year']));
	  				$thisWhereString .= ' >= \'' . $fromDate . ' 00:00:00\' AND ' . $this->get_field_name($path, $fieldname, $fieldid) .' < \'' . $toDate . ' 00:00:00\'';
	  				break;		  					
  			}
  			
  			return $thisWhereString;
	}
	
	function buildWhereClause()
	{
		global $db, $app_list_strings, $beanList, $beanFiles;
				
				$arrayWhere = $this->json_decode_kinamu( html_entity_decode_utf8($this->whereconditions));
		
				if(is_array($this->whereOverride))
		{
			foreach($this->whereOverride as $overrideKey => $overrideData)
			{
				reset($arrayWhere);
				foreach($arrayWhere as $originalKey => $originalData)
				{
					if($originalData['fieldid'] == $overrideData['fieldid'])
					{
						$arrayWhere[$originalKey] = $overrideData;
											}
				}
			}
		}
		
				$arrayWhereGroups = $this->json_decode_kinamu( html_entity_decode_utf8($this->wheregroups));
		
				foreach($arrayWhereGroups as $whereGroupIndex => $thisWhereGroup)
		{
			$thisWhereString = '';
						reset($arrayWhere);
			foreach($arrayWhere as $thisWhere)
			{
								if($thisWhere['groupid'] == $thisWhereGroup['id'])
				{

										if($thisWhereString != '')
						$thisWhereString .= ' ' . $thisWhereGroup['type']  . ' (';
					else
						$thisWhereString .= '(';
					
										$fieldName = substr($thisWhere['path'], strrpos($thisWhere['path'], "::") + 2, strlen($thisWhere['path']));
		  			$pathName = substr($thisWhere['path'], 0, strrpos($thisWhere['path'], "::"));
		  			$fieldArray = explode(':', $fieldName);
					
					if($thisWhere['jointype'] != 'notexisting')
					{			
																		$thisWhereString .= $this->getWhereOperatorClause($thisWhere['operator'], $fieldArray[1], $fieldArray[0], $pathName,  $thisWhere['value'], $thisWhere['valuekey'], $thisWhere['valueto']);
						
					}
					else
					{
												$thisWhereString .= 'not exists(';
						
			  						  			$rightPath = substr($pathName, strrpos($pathName, "::") + 2, strlen($pathName));
			  			$leftPath = substr($pathName, 0, strrpos($pathName, "::"));
			  			
			  						  			$rightArray = explode(':',$rightPath);
			  			$leftArray = explode(':', $leftPath);
			  			
																		$join_params = array(
							'right_join_table_alias' => $this->joinSegments[$leftPath]['alias'], 
							'left_join_table_alias' => $this->joinSegments[$leftPath]['alias'], 
							'join_table_link_alias' => $this->randomstring() , 
							'join_table_alias' => $this->joinSegments[$pathName]['alias']					
						);
						
						$thisWhereString .= $this->joinSegments[$leftPath]['object']->$rightArray[2]->getWhereExistsStatement($join_params);
						
																		$thisWhereString .= 'AND ' . $this->getWhereOperatorClause($thisWhere['operator'], $fieldArray[1], $fieldArray[0], $pathName,  $thisWhere['value'], $thisWhere['valuekey'], $thisWhere['valueto']);
						
												$thisWhereString .= ')';
						
					}
			  			
			  	
										$thisWhereString .= ')';
					
					
				}
			}
			$thisWhereGroup['whereClause'] = $thisWhereString;
			
						$arrayWhereGroupsIndexed[$thisWhereGroup['id']] = $thisWhereGroup;
		}
		
				
				$whereString = $this->buildWhereClauseForLevel($arrayWhereGroupsIndexed['root'], $arrayWhereGroupsIndexed);
		if($whereString != '')
			$whereString = 'WHERE ' . $whereString;
		
		return $whereString;
	}
	
	function buildGroupByClause(){
		global $db, $app_list_strings, $beanList, $beanFiles;
		
				$arrayList =  $this->json_decode_kinamu( html_entity_decode_utf8($this->listfields));	
		
			
				reset($arrayList);
		
				$groupbyString = '';
		
		if(is_array($arrayList))
		{
			
			foreach($arrayList as $thisList)
			{
				if($thisList['groupby'] == 'yes' || $thisList['groupby'] == 'primary' )
				{
										if($groupbyString == '')
						$groupbyString .= 'GROUP BY ';
					else
						$groupbyString .= ', ';
						
									$fieldName = substr($thisList['path'], strrpos($thisList['path'], "::") + 2, strlen($thisList['path']));
		  			$pathName = substr($thisList['path'], 0, strrpos($thisList['path'], "::"));
		  			
		  			$fieldArray = explode(':', $fieldName);
		  			
		  			$groupbyString .= $this->get_field_name($pathName, $fieldArray[1], $fieldArray[0]);
		  							}
			}
		}
		
		return $groupbyString;
		
	}
	
	function buildOrderByClause(){
		global $db, $app_list_strings, $beanList, $beanFiles;
		
				$arrayList =  $this->json_decode_kinamu( html_entity_decode_utf8($this->listfields));	

		
				reset($arrayList);
		
				$orderbyString = '';
		
		if(is_array($arrayList))
		{
			
			foreach($arrayList as $thisList)
			{
				if($thisList['sort'] == 'asc' || $thisList['sort'] == 'desc')
				{
					
					$fieldName = substr($thisList['path'], strrpos($thisList['path'], "::") + 2, strlen($thisList['path']));
		  			$pathName = substr($thisList['path'], 0, strrpos($thisList['path'], "::"));
		  			
		  			$fieldArray = explode(':', $fieldName);
					
					if($thisList['sortpriority'] != '') 
					{
						
						
						if($thisList['sqlfunction'] == '-'  || !$this->evalSQLFunctions)
			  				$sortArray[$thisList['sortpriority']][] =  $this->get_field_name($pathName, $fieldArray[1], $fieldArray[0]) . ' ' . strtoupper($thisList['sort']);
			  			else
			  				$sortArray[$thisList['sortpriority']][] =  $thisList['sqlfunction'] . '(' . $this->get_field_name($pathName, $fieldArray[1], $fieldArray[0]) . ')' . ' ' . strtoupper($thisList['sort']);
					}
					else
					{
						if($thisList['sqlfunction'] == '-' || !$this->evalSQLFunctions)
			  				$sortArray['100'][] =  $this->get_field_name($pathName, $fieldArray[1], $fieldArray[0]) . ' ' . strtoupper($thisList['sort']);
			  			else
			  				$sortArray['100'][] =  $thisList['sqlfunction'] . '(' . $this->get_field_name($pathName, $fieldArray[1], $fieldArray[0]) . ')' . ' ' . strtoupper($thisList['sort']);
						
					}
				}
			}
			
			if(is_array($sortArray))
			{
								$isFirst = true;
				
								ksort($sortArray);
				
				foreach($sortArray as $sortStrings)
				{
					foreach($sortStrings as $sortString)
					{
						if($isFirst)
						{
							$orderbyString .= 'ORDER BY ' . $sortString;
							$isFirst = false;
						}
						else
						{
							$orderbyString .= ', ' . $sortString;
						}
					}
				}
				
			}
		}

		return $orderbyString;
	}
	
function getSelectionResults($parameters, $snapshotid = '0', $getcount = false, $overrideListtype = false){
	
		
	global $db;
	
		if($snapshotid == '0' || $snapshotid == 'current')
	{
				if(!$overrideListtype)
		{
			switch($this->listtype)
			{
				case 'grpwthsum':
				case 'grouped':
					$this->evalSQLFunctions = false;
					$sqlArray = $this->build_sql_string();
					$query = $sqlArray['select'] . ' ' . $sqlArray['from'] . ' ' . $sqlArray['where'] . ' ' . $sqlArray['orderby'];
					break;
				default:
					$this->evalSQLFunctions = true;
					$sqlArray = $this->build_sql_string();
					$query = $sqlArray['select'] . ' ' . $sqlArray['from'] . ' ' . $sqlArray['where'] . ' ' . $sqlArray['groupby'] . ' ' . $sqlArray['orderby'];
					break;
			}
		}
		else
		{
			$this->evalSQLFunctions = true;
			$sqlArray = $this->build_sql_string();
			$query = $sqlArray['select'] . ' ' . $sqlArray['from'] . ' ' . $sqlArray['where'] . ' ' . $sqlArray['groupby'] . ' ' . $sqlArray['orderby'];
			
		}
		
				if($getcount) 
		    return $this->db->getRowCount($db->query($query));
		
		 
		if(isset($parameters['limit']) && $parameters['limit'] != '' && isset($parameters['start']))
		{
			$queryResults = $this->db->limitquery($query, $parameters['start'], $parameters['limit']);
		}
		else 
		{
			switch($this->selectionlimit)
			{
				case 'top10':
					$queryResults = $db->limitquery($query, 0, 10);
					break;
				case 'top20':
					$queryResults = $db->limitquery($query, 0, 20);
					break;						
				case 'top50':
					$queryResults = $db->limitquery($query, 0, 50);
					break;			
				case 'top250':
					$queryResults = $db->limitquery($query, 0, 250);
					break;			
				default: 
					$queryResults = $db->query($query);
					break; 
			}
			
					}
				

		
		while($queryRow = $this->db->fetchByAssoc($queryResults))
		{   
			if(isset($parameters['noFormat']) && $parameters['noFormat'])
				$retArray[] = $queryRow;
			else
				$retArray[] = $this->formatFields($queryRow, (isset($parameters['toPDF']) && $parameters['toPDF']) ? false : true);
		}
	}
	else
	{
			$query = 'SELECT data FROM kreportsnapshotsdata WHERE snapshot_id = \'' . $snapshotid . '\'';
			
						if($getcount) 
		   	 	return $this->db->getRowCount($db->query($query));
			
		   				if(isset($parameters['start']) && $parameters['start'] != '' )
			{
				$query .= ' AND record_id >= ' . $parameters['start'];
			}

			if(isset($parameters['limit']) && $parameters['limit'] != '')
			{
				$query .= ' AND record_id < ' . ($parameters['start'] + $parameters['limit']);
			}			
			
			$query .= ' ORDER BY record_id ASC';
			
			$snapshotResults = $db->query($query);

						$sqlArray = $this->build_sql_string();
			
			while($snapshotRecordData = $db->fetchByAssoc($snapshotResults))
			{
				if(isset($parameters['noFormat']) && $parameters['noFormat'])
					$retArray[] = json_decode_kinamu(html_entity_decode_utf8($snapshotRecordData['data']));
				else
					$retArray[] = $this->formatFields(json_decode_kinamu(html_entity_decode_utf8($snapshotRecordData['data'])), (isset($parameters['toPDF']) && $parameters['toPDF']) ? false : true);
			}
	}
	return $retArray;
	
}

function getSnapshots(){
	$query = 'SELECT id, snapshotdate FROM kreportsnapshots WHERE report_id = \'' . $this->id	. '\' ORDER BY snapshotdate DESC';
	
	$snapShotsResults = $this->db->query($query);
	
	$retArray[] = array('snapshot' => '0', 'description' => 'current');	
	
	while($thisSnapshot = $this->db->fetchByAssoc($snapShotsResults))
	{
		$retArray[] = array('snapshot' => $thisSnapshot['id'], 'description' => $thisSnapshot['snapshotdate']);	
	}
	return $retArray;
}

function getListFields(){
	
					
		
	$arrayList =  $this->json_decode_kinamu( html_entity_decode_utf8($this->listfields));
	
	$retArray[] = array('fieldid' => '-', 'fieldname' => '-');	
	
	
	if(is_array($arrayList))
	{
		foreach($arrayList as $thisList)
		{
												     $retArray[] = array('fieldid' => $thisList['fieldid'], 'fieldname' => $thisList['name']);	
		}
	}	
	else
	{
		$retArray = '';
	}
	

	return $retArray;
}

}
?>
